﻿using System;
using System.Data;
using System.Data.SqlClient;
using ContractManager.Common.Class;
using Speed.Data;

namespace ContractManager.BLL.Business
{
    public static class ClsReports
    {
        
        /// <summary>
        /// Báo cáo chi tiết công văn thầu
        /// </summary>
        /// <param name="fromDate"></param>
        /// <param name="toDate"></param>
        /// <returns></returns>
        public static DataTable GetTenderDocumentDetails(DateTime fromDate, DateTime toDate)
        {
            using (var db = Sys.NewDb())
            {
                string commandText = String.Format("EXEC dbo.usp_Report_TenderDocuments_Details '{0}','{1}'", fromDate.ToString("dd MMM yyyy"),
                                               toDate.ToString("dd MMM yyyy"));
                return db.ExecuteDataTable(commandText);
            }
            
        }

        /// <summary>
        /// Sổ theo dõi tiền về
        /// </summary>
        /// <param name="fromDate">Từ ngày</param>
        /// <param name="toDate">Đến ngày</param>
        /// <param name="mbId">Đơn vị</param>
        /// <param name="storeProcedureName"></param>
        /// <param name="conId">Mã hợp đồng</param>
        /// <param name="conTypeId">Loại hợp đồng</param>
        /// <param name="capId">Loại nguồn vốn</param>
        /// <param name="catId">Loại hình công việc</param>
        /// <param name="workId"></param>
        /// <param name="invID"></param>
        /// <returns></returns>
        public static DataTable GetReportBc01(DateTime fromDate, DateTime toDate, 
                                            string mbId, 
                                            string storeProcedureName, 
                                            int conId,
                                            string conTypeId, string capId, string catId,
                                            string workId, int invID)
        {
            string procedureName = String.IsNullOrEmpty(storeProcedureName) ? "usp_Report_BC01" : storeProcedureName;

            using (var db = Sys.NewDb())
            {
                string commandText = String.Format("EXEC dbo.{0} '{1}','{2}','{3}',{4},'{5}','{6}',N'{7}',N'{8}',{9}",procedureName, fromDate.ToString("dd MMM yyyy"),
                                               toDate.ToString("dd MMM yyyy"), mbId, conId, conTypeId, capId, catId, workId, invID);
                return db.ExecuteDataTable(commandText);
            }
        }



        public static DataTable GetReportBc02(DateTime fromDate, DateTime toDate, int mbId)
        {
            using (var db = Sys.NewDb())
            {
                string commandText = String.Format("EXEC dbo.usp_Report_BC02 '{0}','{1}',{2}", fromDate,
                                               toDate, mbId);
                return db.ExecuteDataTable(commandText);
            }
        }

        public static DataTable GetReportBc03(DateTime fromDate, DateTime toDate)
        {
            using (var db = Sys.NewDb())
            {
                string commandText = String.Format("EXEC dbo.usp_Report_BC03 '{0}','{1}'", fromDate,
                                               toDate);
                return db.ExecuteDataTable(commandText);
            }
        }

        /// <summary>
        /// Báo cáo tổng hợp theo tuần, tháng quý
        /// </summary>
        /// <param name="year">Năm</param>
        /// <param name="fromValue">Giá trị từ</param>
        /// <param name="tType">Loại (1: Tuần; 2: Tháng; 3: Quý)</param>
        /// <param name="storeProcedureName"></param>
        /// <returns></returns>
        public static DataTable GetReportBc05(decimal year, decimal fromValue, int tType, string storeProcedureName)
        {
            string procedureName = String.IsNullOrEmpty(storeProcedureName) ? "usp_Report_BC05" : storeProcedureName;

            using (var db = Sys.NewDb())
            {
                string commandText = String.Format("EXEC dbo.{0} {1},{2},{3}",procedureName, year,
                                               fromValue,  tType);
                return db.ExecuteDataTable(commandText);
                
            }
        }
        /// <summary>
        /// Báo cáo sản xuất kinh doanh
        /// </summary>
        /// <param name="year"></param>
        /// <param name="fromValue"></param>
        /// <param name="tType"></param>
        /// <param name="storeProcedureName"></param>
        /// <returns></returns>
        public static DataSet GetBusinessReport(decimal year, decimal fromValue, int tType, string storeProcedureName)
        {
            DataSet ds = new DataSet();
            string procedureName = String.IsNullOrEmpty(storeProcedureName) ? "usp_Report_BC05" : storeProcedureName;
            using (SqlConnection con = new SqlConnection(ClsVariable.StrSqlConnectionString))
            {
                SqlCommand cmd = new SqlCommand(procedureName, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Year", year);
                cmd.Parameters.AddWithValue("@FromValue", fromValue);
                cmd.Parameters.AddWithValue("@dType", tType);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds);
            }
            return ds;
        }

        /// <summary>
        /// Báo cáo công văn đến liên quan tới hợp đồng
        /// </summary>
        /// <param name="fromDate">Từ ngày</param>
        /// <param name="toDate">Đến ngày</param>
        /// <param name="mbId">Đơn vị</param>
        /// <param name="conId">Mã hợp đồng</param>
        /// <returns></returns>
        public static DataTable GetReportBc13(DateTime fromDate, DateTime toDate, string mbId, int conId)
        {
            using (var db = Sys.NewDb())
            {
                string commandText = String.Format("EXEC dbo.usp_Report_BC13 '{0}','{1}','{2}',{3}", fromDate.ToString("dd MMM yyyy"),
                                               toDate.ToString("dd MMM yyyy"), mbId,conId);
                return db.ExecuteDataTable(commandText);
            }
        }

        /// <summary>
        /// Báo cáo phân tích dạng Pivot Table
        /// </summary>
        /// <param name="fromDate">Từ ngày</param>
        /// <param name="toDate">Đến ngày</param>
        /// <param name="mbId">Mã đơn vị</param>
        /// <param name="dtype">Loại ngày</param>
        /// <param name="storeProcedureName"></param>
        /// <returns></returns>
        public static DataTable GetReportPivotGrid(DateTime fromDate, DateTime toDate, string mbId, int dtype,string storeProcedureName)
        {
            string procedureName = String.IsNullOrEmpty(storeProcedureName) ? "usp_Report_PivotGrid" : storeProcedureName;
            using (var db = Sys.NewDb())
            {
                string commandText = String.Format("EXEC dbo.{0} '{1}','{2}','{3}',{4}", procedureName, fromDate.ToString("dd MMM yyyy"),
                                               toDate.ToString("dd MMM yyyy"), mbId, dtype);
                return db.ExecuteDataTable(commandText);
            }
        }


        public static DataTable DoPrintQdgnv(int conDetId)
        {
            string procedureName = "usp_Report_PrintDecision";

            using (var db = Sys.NewDb())
            {
                string commandText = String.Format("EXEC dbo.{0} {1}", procedureName, conDetId);
                return db.ExecuteDataTable(commandText);
            }
        }


        public static DataTable DoPrintQdgk(int conDetId)
        {
            string procedureName = "usp_Report_PrintQDGK";

            using (var db = Sys.NewDb())
            {
                string commandText = String.Format("EXEC dbo.{0} {1}", procedureName, conDetId);
                return db.ExecuteDataTable(commandText);
            }
        }
    }
}
